﻿using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.IO;

using Microsoft.AnalysisServices.AdomdClient;

public partial class ws_BI_CashAtYear : System.Web.UI.Page
{

    #region Members
    string ConnStr = GetAppSetting("BIConnect");
    string strYear, strMonth;
    #endregion

    #region Common
    static public string GetAppSetting(String mName)
    {
        String vStr = null;
        string strPath =
    @"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(strPath);
        XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
        foreach (XmlNode keynode in node.ChildNodes)
        {
            string keyStr = keynode.Attributes["key"].Value;
            if (keyStr == mName)
            {
                vStr = keynode.Attributes["value"].Value;
                break;
            }
        }

        if (String.IsNullOrEmpty(vStr))
            vStr = "";

        return vStr;
    }
    #endregion

    #region Page Events

    protected override void OnPreInit(EventArgs e)
    {
        // Enabled trace in code
        this.Context.Trace.IsEnabled = false;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.Params["Year"] != null)
            strYear = Request.Params["Year"].ToString();
        else
            strYear = DateTime.Now.Year.ToString();

        if (Request.Params["Month"] != null)
            strMonth = Request.Params["Month"].ToString();
        else
            strMonth = DateTime.Now.Month.ToString();

        XmlDocument xReturn = getXML();
        Response.ContentType = "text/xml";
        Response.Write(xReturn.OuterXml);
    }

    #endregion

    #region Data

    public XmlDocument getXML()
    {
        int Year = Convert.ToInt32(strYear);
        int Month = Convert.ToInt32(strMonth);

        XmlDocument xReturn = new XmlDocument();
        DataTable tbl = new DataTable("tbl");

        string connStr = GetAppSetting(@"BIConnect");
        DateTime dt = DateTime.Now;

        Exception err = new BI_SQL_CashAtYear().Get(ref tbl, connStr, Year, Month);

        if (err != null)
        {
            tbl = new DataTable();

            tbl.Columns.Add("ID", typeof(string));
            tbl.Columns[0].Caption = "ID";
            tbl.Columns.Add("Name", typeof(string));
            tbl.Columns[1].Caption = "Name";

            DataRow r = tbl.NewRow();
            r[0] = "-1000";
            r[1] = "Ошибка получения данных.";
            tbl.Rows.Add(r);
        }

        using (StringWriter stringWriter = new StringWriter())
        {
            using (XmlTextWriter xmlTextwriter = new XmlTextWriter(stringWriter))
            {
                tbl.WriteXml(xmlTextwriter, XmlWriteMode.WriteSchema);
                xReturn.LoadXml(stringWriter.ToString());
                XmlNode xErrors = xReturn.CreateElement("Errors");
                if (err != null)
                {
                    XmlNode xErr = xReturn.CreateElement("Err");
                    xErr.InnerText = err.Message;
                    xErrors.AppendChild(xErr);
                }
                xReturn.ChildNodes[0].AppendChild(xErrors);
            }
        }

        return xReturn;
    }

    #endregion

}

public class BI_SQL_CashAtYear
{

    #region Common

    public static string MakeDT_Y_M(int Year, int Month)
    {
        return string.Format("{0:0000}{1:00}", Year, Month);
    }

    #endregion

    public Exception Exec(ref CellSet parCst, string parConnStr, string parCmdStr)
    {
        Exception ret = null;
        try
        {
            using (AdomdConnection conn = new AdomdConnection(parConnStr))
            {
                conn.Open();
                using (AdomdCommand cmd = new AdomdCommand(parCmdStr, conn))
                {
                    parCst = cmd.ExecuteCellSet();
                    conn.Close();
                }
            }
        }
        catch (Exception err)
        {
            parCst = null;
            ret = new Exception(err.Message, err);
        }

        return ret;
    }

    public Exception Get(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
    {
        #region Members

        Exception ret = null;
        string MonthStr = MakeDT_Y_M(parYear, parMonth);

        #region SQL
        /*
SELECT NON EMPTY 
Hierarchize({DrilldownLevel({[Дата].[Год].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
ON COLUMNS , 
NON EMPTY Hierarchize({DrilldownLevel({[Дата].[Месяц].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Месяц].[Месяц].[Номер месяца] 
ON ROWS 
FROM [KPI] 
WHERE ([Measures].[Оборот7 Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

            */
        string cmdStr =
@"
SELECT NON EMPTY 
Hierarchize({DrilldownLevel({[Дата].[Год].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
ON COLUMNS , 
NON EMPTY Hierarchize({DrilldownLevel({[Дата].[Месяц].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Месяц].[Месяц].[Номер месяца] 
ON ROWS 
FROM [KPI] 
WHERE ([Measures].[Оборот7 Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
";

        #endregion SQL

        #endregion Members

        CellSet cst = null;
        Exception err = Exec(ref cst, parConnStr, cmdStr);

        if (err != null)
            ret = new Exception(err.Message);
        else
        {
            err = Parse_001(ref cst, ref tbl);
            if (err != null)
                ret = new Exception(err.Message);
        }

        return ret;
    }

    public Exception Parse_001(ref CellSet parCst, ref DataTable parTbl)
    {
        Exception ret = null;

        #region Init

        // check if any axes were returned else throw error.
        int axes_count = parCst.Axes.Count;
        if (axes_count == 0)
            return new Exception("No data returned for the selection");

        // if axes count is not 2
        if (axes_count != 2)
            return new Exception("The sample code support only queries with two axes");

        // if no position on either row or column throw error
        if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
            return new Exception("No data returned for the selection");

        #endregion

        #region Members

        int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
        cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

        // Number of dimensions on the column
        col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

        // Number of dimensions on the row
        if (parCst.Axes[1].Positions[0].Members.Count > 0)
            row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

        // Total rows and columns
        row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
        col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

        MakeTbl_001(ref parCst, ref parTbl, col_count);

        int count = 0;
        DataRow r = null;

        #endregion

        #region for

        for (cur_row = 0; cur_row < row_count - 1; cur_row++)
        {

            count++;
            if (count >= 1)
                r = parTbl.NewRow();
            else
                continue;

            #region Data

            for (int i = 0; i < col_count - 1; i++)
            {
                if (parCst[i, cur_row].Value != null)
                    r[i + 1] = Convert.ToDecimal(parCst[i, cur_row].Value);
                else
                    r[i + 1] = DBNull.Value;
            }

            r[0] = parCst.Axes[1].Positions[cur_row].Members[0].Caption;

            parTbl.Rows.Add(r);

            #endregion

        }

        parTbl.Columns.Remove("All");

        #endregion

        return ret;
    }

    public void MakeTbl_001(ref CellSet parCst, ref DataTable parTbl, int cnt)
    {
        int row_dim_count = 0, col_count;

        if (parTbl == null)
            parTbl = new DataTable();

        parTbl.Columns.Add("Month", typeof(string));
        parTbl.Columns.Add("All", typeof(string));

        if (parCst.Axes[1].Positions[0].Members.Count > 0)
            row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

        col_count = parCst.Axes[0].Positions.Count + row_dim_count;

        for (int i = 1; i < col_count - 1; i++)
        {
            string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
            parTbl.Columns.Add(ColNameStr, typeof(Decimal));
        }

    }

}
